package com.fpno5.dbutils;

import com.fpno.bean.Customer;
import com.fpno4.util.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.*;
import org.junit.Test;

import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/*
* commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库 封装了针对于数据库的增删改查操作
* */
public class QueryRunnerTest {
    //测试插入
    @Test
    public void testInsert() {
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnection3();
            String sql = "insert into customers(name,email,birth)values(?,?,?)";
            int count = runner.update(conn, sql, "huge", "huge@qq.com", "1989-4-6");
            System.out.println(count);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            com.fpno.util.JDBCUtils.closeResource(conn, null);
        }
    }

    //测试查询
    /*
     * BeanHandler:是ResultSetHandler接口的实现，用于封装表中的一条记录
     * */
    @Test
    public void testQuery() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection3();
            String sql = "select id,name,email,birth from customers where id=?";
            BeanHandler<Customer> handler = new BeanHandler<Customer>(Customer.class);
            QueryRunner runner = new QueryRunner();
            Customer customer = runner.query(conn, sql, handler, 7);
            System.out.println(customer);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            com.fpno.util.JDBCUtils.closeResource(conn, null);
        }
    }

    /*
     * BeanListHandler:是ResultSetHandler接口的实现，用于封装表中的多条记录
     * */
    @Test
    public void testQuery2() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection3();
            String sql = "select id,name,email,birth from customers where id<?";
            BeanListHandler<Customer> handler = new BeanListHandler<Customer>(Customer.class);
            QueryRunner runner = new QueryRunner();
            List<Customer> list = runner.query(conn, sql, handler, 7);
            list.forEach(System.out::println);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            com.fpno.util.JDBCUtils.closeResource(conn, null);
        }
    }


    /*
     * MapHandler:是ResultSetHandler接口的实现，对应表中的一条记录
     * 将字段及相应的字段中的值作为map的key和value
     * */
    @Test
    public void testQuery3() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection3();
            String sql = "select id,name,email,birth from customers where id=?";
            MapHandler handler = new MapHandler();
            QueryRunner runner = new QueryRunner();
            Map<String, Object> map = runner.query(conn, sql, handler, 7);
            System.out.println(map);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            com.fpno.util.JDBCUtils.closeResource(conn, null);
        }
    }

    /*
     * MapHandler:是ResultSetHandler接口的实现，对应表中的多条记录
     * 将字段及相应的字段中的值作为map的key和value
     * */
    @Test
    public void testQuery4() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection3();
            String sql = "select id,name,email,birth from customers where id<?";
            MapListHandler handler = new MapListHandler();
            QueryRunner runner = new QueryRunner();
            List<Map<String, Object>> list = runner.query(conn, sql, handler, 7);
            list.forEach(System.out::println);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            com.fpno.util.JDBCUtils.closeResource(conn, null);
        }
    }

    /*
     *ScalarHandler查询特殊值
     * */
    @Test
    public void testQuery5() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection3();
            String sql = "select count(*) from customers";
            ScalarHandler handler = new ScalarHandler();
            QueryRunner runner = new QueryRunner();
            Long count = (Long) runner.query(conn, sql, handler);
            System.out.println(count);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            com.fpno.util.JDBCUtils.closeResource(conn, null);
        }
    }

    /*
    * 自定义ResultSetHandler的实现
    * */
    @Test
    public void testQuery6() {
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnection3();
            String sql="select id,name,email,birth from customers where id=?";
            ResultSetHandler<Customer> handler=new ResultSetHandler<Customer>() {
                @Override
                public Customer handle(ResultSet rs) throws SQLException {
                    if (rs.next()){
                        int id = rs.getInt("id");
                        String name = rs.getString("name");
                        String email = rs.getString("email");
                        Date birth = rs.getDate("birth");
                        Customer customer = new Customer(id, name, email, birth);
                        return customer;
                    }
                    return null;
                }
            };
            Customer customer = runner.query(conn, sql, handler, 7);
            System.out.println(customer);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            com.fpno.util.JDBCUtils.closeResource(conn, null);
        }
}
}